So far, in this series about the IBM Data Studio integrated development environment (IDE) that’s available with DB2 Version 9.5 (DB2 9.5), I’ve shown you how to set up database connection objects and the actions that you can perform on them. In addition, I introduced you (in Part 2) to some of the features available in the Database Explorer view, and Part 3 and Part 4 gave you a full tour of overview diagrams. In this article, I want to introduce you to the OLE DB function capability available in IBM Data Studio.

Object Linking and Embedding Database (OLE DB) Functions

Microsoft OLE DB is a set of application programming interfaces through which applications can uniformly access data that is stored in diverse information sources. OLE DB is conceptually divided into OLE DB providers and OLE DB consumers. An OLE DB provider is a software component that exposes OLE DB interfaces, and an OLE DB consumer is the application that accesses the OLE DB interfaces and consumes the data.

The IBM Data Studio gives you the ability, without writing a single line of code, to create OLE DB functions within a DB2 data server that talks to OLE DB providers. You can use this function to create a table and populate it with the target OLE DB data source’s data and persist that data locally in a DB2 table. You can also create a view that calls the OLE DB function and provides access to the target data in the same manner as a regular DB2 view.

Today, most data sources have some sort of OLE DB provider – with DB2 9.5, it’s automatically installed with an IBM Data Server client. For example, using IBM Data Studio, you can define an OLE DB table function to populate a local table automatically with data from a Microsoft Access table. (I will show you how to do that in this article.) In fact, lots of applications (not just data servers) have OLE DB providers today. For example, the Microsoft Exchange address book has a provider, which means that you can easily create a report in DB2 9.5 that seamlessly combines data from an OLE DB table function built over your Exchange address book and all the logic that lies within your DB2 database.

Using OLE DB table functions in DB2 9.5 reduces your application development effort by providing built-in access to most OLE DB providers. With OLE DB table functions, you use a generic OLE DB consumer interface that’s built into DB2 9.5 to retrieve data from most OLE DB providers. You only need to register a table function as LANGUAGE OLEDB, and refer to the OLE DB provider and the relevant row set as a data source; of course, IBM Data Studio takes care of all of this for you. Quite simply, you don’t have to do any function programming to take advantage of OLE DB table functions in DB2 9.5.

Once you get to know these functions in DB2 9.5, you may think they are some sort of panacea for integration. Well, they can be. However, you have to understand that the functionality provided by this method can only be as good as the OLE DB provider that’s installed with the target data source. Considering the fact that Microsoft is focusing on the .NET API these days, vendors aren’t investing in OLE DB of late. Of course, if you want a scalable and rich integration platform, look at the capabilities of the IBM Information Server, which provides native access to target data sources with function compensation and optimizations to make it even easier than this method. With that said, if you want a quick-and-dirty way to grab that data from Microsoft Access (or from any other OLE DB data source, for that matter), this may just do the trick.

Things you have to do to follow the examples in this article...

In case you haven’t read Parts 1 to 4 in this series, I assume, for this article, that you’ve at least created the SAMPLE database (using the db2sampl –xml –sql command) and that the SAMPLE database appears in the Database Explorer view. I’m also assuming that you have access to the NORTHWIND database that comes with Microsoft Access (which further assumes that you have Microsoft Access installed). Finally, the db2olefn.dll file must be registered on your computer before you can create OLE DB functions using IBM Data Studio. To register this DLL file, enter the following command from your operating system’s command line processor (CLP): regsvr32 \sqllib\function\db2olefn.dll You will get a message confirming the registration of this DDL file when it is complete:

Full article...